package com.scalar.chiptrack.finance.creditNote.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.sales.SalesOrder;
import com.scalar.chiptrack.sales.dao.SalesOrderDAO;
import com.scalar.chiptrack.finance.creditNote.forms.CreditNoteForm;
import com.scalar.chiptrack.finance.creditNote.CreditNote;
import com.scalar.chiptrack.operations.packingslip.dao.PackingSlipDAO;
import com.scalar.chiptrack.operations.packingslip.dao.NewPackingSlipDAO;
import com.scalar.chiptrack.operations.packingslip.forms.PackingSlipForm;

import java.sql.*;
import java.util.Calendar;
import java.util.ArrayList;

/**
 * User: Sujan
 * Date: Apr 16, 2007
 * Time: 7:28:04 PM
 */
public class CreditNoteDAO
{
    /**
     * This method will return the auto generated Credit Note number to be used to create new Credit Note. <br>
     * Auto Generated Credit Note Number will be in the format: <b>&lt;Credit Note PREFIX&gt;-&lt;yMMdd&gt;&lt;seq&gt; </b><br>
     * where <b>&lt;Credit Note PREFIX&gt;</b> - Credit Note Prefix which is set according to the noteType. if it is empty then hyphen(-) is not added to the auto Credit Note number.<br>
     * <b>&lt;yMMdd&gt;</b> - single digit year(y) Two digit month(MM) Two digit day(dd). month range will be 01-12 and day will be 01-31<br>
     * <b>&lt;seq&gt;</b> - sequence number. range will be 00 - 99<br>
     * For Ex: CN-7011700 --> Credit Note - Prefix <br>
     *                    --> 7 - year(2007)<br>
     *                    --> 01 - month(January)<br>
     *                    --> 17 - day<br>
     *                    --> 00 - sequence</br>
     * when Credit Note prefix is not given then the auto Credit Note number will be: <b>7011700</b>
     * @return returns the auto generated Credit Note number.
     * @param noteType
     */
    public static String generateAutomatedCreditNoteNo ( String noteType ) throws ScalarException {
        String newCreditNoteNumber = null;
        String sequence = "00";
        String creditNoteNumber = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String creditNotePrefix = null;
        int creditNoteLength = 0;
        Calendar cl	= Calendar.getInstance();
        newCreditNoteNumber = StringUtils.formatDateToString(cl.getTime(), "yyMMdd" );
        newCreditNoteNumber = newCreditNoteNumber.substring(1);

        try
        {
            conn = DataSource.getConnection();
            /*pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
            rset = pstmt.executeQuery();
            if( rset.next())
            {
                creditNotePrefix = rset.getString("Sales_Order_Prefix");
                creditNoteLength = rset.getInt("Sales_Order_Length");
            }

            try { rset.close(); rset = null; } catch ( SQLException ignore ) {}
            try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}*/

            if ( "Credit".equalsIgnoreCase( noteType ) )
            {
                creditNotePrefix = "CR";
            }
            else
            {
                creditNotePrefix = "DR";
            }

            pstmt = conn.prepareStatement( "SELECT TOP 1 CREDITNOTE_NO FROM CREDIT_NOTE_TABLE WHERE LEN(SUBSTRING(CREDITNOTE_NO,CHARINDEX('-',CREDITNOTE_NO)+1,LEN(CREDITNOTE_NO))) = 7 ORDER BY CREDITNOTE_ID DESC" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                creditNoteNumber = rset.getString ( "CREDITNOTE_NO" );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not get Automated CREDIT NOTE NO from database", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        if ( creditNoteNumber != null )
        {
            int seq = 0;
            try
            {
                seq = Integer.parseInt ( creditNoteNumber.substring( creditNoteNumber.length()-2 ) );
            } catch ( NumberFormatException e ) { seq = -1; }
            seq = seq + 1;
            if ( seq > 99 )
            {
                seq = 0;
            }
            sequence = (seq<10) ? "0"+seq : Integer.toString(seq);
        }
        newCreditNoteNumber = newCreditNoteNumber + sequence;
        if( creditNotePrefix != null && creditNotePrefix.trim().length() != 0 )
        {
            newCreditNoteNumber = creditNotePrefix + "-" + newCreditNoteNumber;
        }
        return newCreditNoteNumber;
    }

    public static ArrayList loadLineItemsforNewCreditNote ( DBTransaction dbTransaction, int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList lineItems = new ArrayList();
        LineItem lineItem = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT COOL.LINEITEM_ID, COOL.COOL_LINE_ITEM_NO, COOL.COOL_PO_LINE_ITEM_NO, P.PART_NUMBER, COOL.COOL_CUSTOMER_PARTNUMBER, COOL.COOL_DESCRIPTION,\n" +
                    "    CSLIT_QTY_SHIPPED AS SHIPPED_QTY, CONVERT(VARCHAR,CST_CONFIRMED_ON, 101) AS SHIP_DATE, COOL.COOL_UNIT_COST,(CSLIT_QTY_SHIPPED*COOL.COOL_UNIT_COST) AS SHIP_AMOUNT    \n" +
                    "    FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, \n" +
                    "    CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, PRODUCT P\n" +
                    "    WHERE COOL.SALESORDER_ID = CST.SALESORDER_ID    \n" +
                    "    AND COOL.LINEITEM_ID=CSLIT.LINEITEM_ID\n" +
                    "    AND  CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID\n" +
                    "    AND CST.CST_STATUS = 'SHIPPED'\n" +
                    "    AND P.PRODUCT_ID = COOL.PRODUCT_ID\n" +
                    "    AND CST.CUSTOMERSHIPPING_ID = ?\n" +
                    " ORDER BY COOL.COOL_LINE_ITEM_NO" );
            pstmt.setInt ( 1, customerShippingId );

            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                lineItem = new LineItem();
                lineItem.setLineItemId( rset.getInt( "LINEITEM_ID" ) );
                lineItem.setLineItemNo( rset.getInt( "COOL_LINE_ITEM_NO" ) );
                lineItem.setPoLineItemNo( rset.getInt( "COOL_PO_LINE_ITEM_NO" ) );
                lineItem.setManufacturingPartNo( rset.getString ( "PART_NUMBER") );
                lineItem.setCustomerPartNo( rset.getString ( "COOL_CUSTOMER_PARTNUMBER") );
                lineItem.setDescription( rset.getString ( "COOL_DESCRIPTION" ) );
                lineItem.setShippedQty( rset.getInt ( "SHIPPED_QTY" ) );
                lineItem.setShippedDate( rset.getString ( "SHIP_DATE" ) );
                lineItem.setUnitPriceAsDouble( rset.getDouble( "COOL_UNIT_COST" ) );
                lineItem.setAmountAsDouble( rset.getDouble( "SHIP_AMOUNT" ) );

                lineItems.add ( lineItem );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load line items for Credit note", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return lineItems;
    }

    public static boolean isCreditNoteNoExists ( DBTransaction dbTransaction, String creditNoteNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean isExists = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS COUNT FROM CREDIT_NOTE_TABLE WHERE CREDITNOTE_NO = ?" );
            pstmt.setString ( 1, creditNoteNumber );

            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                isExists = rset.getInt ( "COUNT" ) > 0;
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not check Credit Note Number", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return isExists;
    }

    public static int saveCreditNote(DBTransaction dbTransaction, String creditNoteNumber, String issueDate, int salesOrderId, String userId, String noteType, String comments, int customerShippingId, boolean isUnits  ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int creditNoteId = -1;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "INSERT INTO CREDIT_NOTE_TABLE ( CREDITNOTE_NO, ISSUE_DATE, CREATED_BY, SALESORDER_ID, STATUS, [TIMESTAMP], IS_CREDIT_NOTE, COMMENTS, CUSTOMERSHIPPING_ID, IS_UNITS ) VALUES ( ?, ?, ?, ?, 'HOLD', CURRENT_TIMESTAMP, ?, ?, ?, ? )" );
            pstmt.setString ( 1, creditNoteNumber );
            pstmt.setString ( 2, issueDate );
            pstmt.setString ( 3, userId );
            pstmt.setInt ( 4, salesOrderId );
            pstmt.setInt ( 5, "Credit".equalsIgnoreCase( noteType ) ? 0 : 1 );
            pstmt.setString(6, comments);
            pstmt.setInt ( 7, customerShippingId );
            pstmt.setInt ( 8, isUnits ? 0 : 1 );
            pstmt.executeUpdate();

            try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}

            pstmt = conn.prepareStatement( "SELECT @@IDENTITY AS CREDITNOTE_ID" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                creditNoteId = rset.getInt ( "CREDITNOTE_ID" );
            }
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not check Credit Note Number", e );
        }
        finally
        {
            if ( dbTransaction == null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return creditNoteId;
    }

    public static void saveCreditNoteLineItems ( DBTransaction dbTransaction, CreditNoteForm creditNoteForm, String userId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{CALL SP_SAVE_CREDIT_NOTE_LINEITEMS(?, ?, ?, ?, ?, ?, ?, ?)}" );
            int creditNoteId = creditNoteForm.getCreditNoteId();
            ArrayList lineItems = creditNoteForm.getLineItems();
            int lineItemsSize = (lineItems == null) ? 0 : lineItems.size();
            for ( int i=0; i < lineItemsSize; i++ )
            {
                LineItem lineItem = (LineItem)lineItems.get ( i );
                if ( lineItem.getIsCreditNote() )
                {
                    cstmt.setInt    ( 1, creditNoteId );
                    cstmt.setInt    ( 2, lineItem.getLineItemId() );
                    cstmt.setDouble ( 3, lineItem.getDiscountPrice() );
                    cstmt.setDouble ( 4, "Credit".equalsIgnoreCase( creditNoteForm.getNoteType() ) ? ( - lineItem.getDiscountAmount() ) : lineItem.getDiscountAmount() );
                    cstmt.setString ( 5, lineItem.getReason() );
                    cstmt.setString ( 6, userId );
                    cstmt.setInt    ( 7, "Credit".equalsIgnoreCase( creditNoteForm.getNoteType() ) ? ( - lineItem.getAdjustmentQty() ) : lineItem.getAdjustmentQty()  );
                    cstmt.setInt    ( 8, lineItem.getIsCancel() ? 0 : 1 );
                    
                    cstmt.addBatch();
                }
            }

            cstmt.executeBatch();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Credit Note Line Items", e );
        }
        finally
        {
            if ( dbTransaction == null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
    }
    public static void loadCreditNote( DBTransaction dbTransaction, CreditNoteForm creditNoteForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int creditNoteId = -1;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT CREDITNOTE_NO, CONVERT( VARCHAR,ISSUE_DATE,101 ) AS ISSUE_DATE, SALESORDER_ID, IS_CREDIT_NOTE, COMMENTS, CUSTOMERSHIPPING_ID, IS_UNITS FROM CREDIT_NOTE_TABLE CNT WHERE CREDITNOTE_ID = ?" );
            pstmt.setInt ( 1, creditNoteForm.getCreditNoteId() );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                creditNoteForm.setCreditNoteNumber( rset.getString ( "CREDITNOTE_NO" ) );
                creditNoteForm.setIssueDate( rset.getString ( "ISSUE_DATE" ) );
                creditNoteForm.setSalesOrderId( rset.getInt ( "SALESORDER_ID" ) );
                creditNoteForm.setNoteType( rset.getInt ( "IS_CREDIT_NOTE" ) == 0 ? "Credit" : "Debit" );
                creditNoteForm.setComments( rset.getString ( "COMMENTS" ) );
                creditNoteForm.setCustomerShippingId( rset.getInt ( "CUSTOMERSHIPPING_ID" ) );
                creditNoteForm.setIsUnits( rset.getInt ( "IS_UNITS" ) == 0 ? true : false );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Credit Note", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }

        PackingSlipForm packingSlipForm = NewPackingSlipDAO.loadPackingSlipById( creditNoteForm.getCustomerShippingId() );
        creditNoteForm.setSalesOrderId( packingSlipForm.getSalesOrderId() );
        creditNoteForm.setBillingAddress( packingSlipForm.getBillingAddressObject() );
        creditNoteForm.setShippingAddress( packingSlipForm.getShippingAddressObject() );
        creditNoteForm.setPackingSlipLabel( packingSlipForm.getLabel() );

        SalesOrder salesOrder = SalesOrderDAO.loadSalesOrder( creditNoteForm.getSalesOrderId()+"" );

        creditNoteForm.setCustomerName( salesOrder.getCustomerName() );
        creditNoteForm.setSalesOrderNo( salesOrder.getSalesOrderNo() );
        creditNoteForm.setCustomerPONo( salesOrder.getCustomerPO() );
        creditNoteForm.setSalesRep( salesOrder.getSalesRep() );
        creditNoteForm.setEndCustomerName( salesOrder.getOdm());
        creditNoteForm.setEndUserName( salesOrder.getEndCustomer() );
        creditNoteForm.setPayTerms( salesOrder.getShippingTerms());

        ArrayList allLineItems = loadCreditNoteLineItems(dbTransaction, creditNoteForm.getCreditNoteId() );
        ArrayList cancelledLineItems = new ArrayList();
        ArrayList lineItems = new ArrayList();
        int size = (allLineItems != null) ? allLineItems.size() : 0;
        double totalAdjustmentAmt = 0d;
        for ( int i=0; i < size; i++ )
        {
            LineItem lineItem = (LineItem)allLineItems.get(i);
            if ( lineItem.getIsCancel() )
            {
                cancelledLineItems.add ( lineItem );
            }
            else
            {
                lineItems.add ( lineItem );
                totalAdjustmentAmt += lineItem.getDiscountAmount();
            }
        }
        creditNoteForm.setCancelledLineItems( cancelledLineItems );
        creditNoteForm.setLineItems( lineItems );
        creditNoteForm.setTotalAdjustmentAmount( Double.parseDouble(StringUtils.formatAmount(totalAdjustmentAmt, "###########.###")) );
    }

    /**
     * loads all the credit note line items including cancelled also.
     * @param dbTransaction
     * @param creditNoteId
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadCreditNoteLineItems ( DBTransaction dbTransaction, int creditNoteId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        ArrayList lineItems = new ArrayList();
        LineItem lineItem = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{CALL SP_LOAD_CREDIT_NOTE_LINE_ITEMS(?)}" );
            cstmt.setInt ( 1, creditNoteId );
            rset = cstmt.executeQuery();

            while ( rset.next() )
            {
                lineItem = new LineItem();
                lineItem.setLineItemId( rset.getInt( "LINEITEM_ID" ) );
                lineItem.setLineItemNo( rset.getInt( "COOL_LINE_ITEM_NO" ) );
                lineItem.setPoLineItemNo( rset.getInt( "COOL_PO_LINE_ITEM_NO" ) );
                lineItem.setManufacturingPartNo( rset.getString ( "PART_NUMBER") );
                lineItem.setCustomerPartNo( rset.getString ( "COOL_CUSTOMER_PARTNUMBER") );
                lineItem.setDescription( rset.getString ( "COOL_DESCRIPTION" ) );
                lineItem.setShippedQty( rset.getInt ( "SHIPPED_QTY" ) );
                lineItem.setShippedDate( rset.getString ( "SHIP_DATE" ) );
                lineItem.setUnitPriceAsDouble( rset.getDouble( "COOL_UNIT_COST" ) );
                lineItem.setAmountAsDouble( rset.getDouble( "SHIP_AMOUNT" ) );
                lineItem.setReason( rset.getString ( "REASON" ) );
                lineItem.setAdjustmentQty( rset.getInt ( "ADJUSTMENT_QTY" ) );
                lineItem.setDiscountPrice( rset.getDouble( "DISCOUNT_PER_UNIT" ) );
                lineItem.setDiscountAmount( rset.getDouble( "DISCOUNT_AMOUNT" ) );
                lineItem.setStatus( rset.getString ( "STATUS" ) );
                lineItem.setFinanceHold( rset.getString ( "FIN_HOLD_REASON_CODE" ) );
                lineItem.setMarketingHold( rset.getString ( "MKTG_HOLD_REASON_CODE" ) );
                lineItem.setOperationsHold( rset.getString ( "OPS_HOLD_REASON_CODE" ) );
                lineItem.setSalesHold( rset.getString ( "SALES_HOLD_REASON_CODE" ) );
                lineItem.setIsCreditNote( true );
                lineItem.setIsCancel( "CANCELLED".equalsIgnoreCase( lineItem.getStatus() ) );

                lineItems.add ( lineItem );
            }

            cstmt.executeBatch();
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Credit Note Line Items", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return lineItems;
    }

    public static ArrayList loadCreditNotesList(DBTransaction dbTransaction, int customerId, String salesOrderNo, String creditNoteNumber, String noteType, String status, String fromDate, String toDate) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        ArrayList creditNotesList = new ArrayList();
        CreditNote creditNote = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall ( "{ CALL SP_LOAD_CREDIT_NOTES( ?, ?, ?, ?, ?, ?, ? ) }" );
            cstmt.setInt ( 1, customerId );
            cstmt.setString ( 2, salesOrderNo );
            cstmt.setString ( 3, creditNoteNumber );
            cstmt.setString ( 4, noteType );
            cstmt.setString( 5, status );
            cstmt.setString ( 6, fromDate );
            cstmt.setString ( 7, toDate );
            rset = cstmt.executeQuery();

            while ( rset.next() )
            {
                creditNote = new CreditNote();
                creditNote.setCreditNoteId( rset.getInt ( "CREDITNOTE_ID" ) );
                creditNote.setCreditNoteNumber( rset.getString ( "CREDITNOTE_NO" ) );
                creditNote.setSalesOrderNo( rset.getString ( "SALESORDER_NO" ) );
                creditNote.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );
                creditNote.setStatus( rset.getString ( "STATUS" ) );
                creditNote.setFinalStatus( rset.getString ( "FINAL_STATUS" ) );
                creditNote.setNoteType( (rset.getInt ( "IS_CREDIT_NOTE") == 0) ? "Credit" : "Debit" );
                creditNote.setIssueDate( rset.getString ( "ISSUE_DATE" ) );
                creditNote.setTotalAmount( rset.getDouble( "TOTAL_AMOUNT" ) );
                creditNotesList.add ( creditNote );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Credit Notes", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return creditNotesList;
    }
    public static void updateCreditNote ( DBTransaction dbTransaction, int creditNoteId, String issueDate, String comments ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "UPDATE CREDIT_NOTE_TABLE SET ISSUE_DATE = ?, [TIMESTAMP] = CURRENT_TIMESTAMP, COMMENTS = ? WHERE CREDITNOTE_ID = ?" );
            pstmt.setString ( 1, issueDate );
            pstmt.setString ( 2, comments );
            pstmt.setInt ( 3, creditNoteId );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not check Credit Note Number", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
    }
    public static void approveCreditNoteLineItems ( DBTransaction dbTransaction, CreditNoteForm creditNoteForm, String userId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{CALL SP_APPROVE_CREDIT_NOTE_LINEITEMS(?, ?, ?, ?, ?, ?,?)}" );
            int creditNoteId = creditNoteForm.getCreditNoteId();
            ArrayList lineItems = creditNoteForm.getLineItems();
            int lineItemsSize = (lineItems == null) ? 0 : lineItems.size();
            for ( int i=0; i < lineItemsSize; i++ )
            {
                LineItem lineItem = (LineItem)lineItems.get ( i );
                cstmt.setInt ( 1, creditNoteId );
                cstmt.setInt ( 2, lineItem.getLineItemId() );
                cstmt.setString ( 3, lineItem.getMarketingHold() );
                cstmt.setString ( 4, lineItem.getFinanceHold() );
                cstmt.setString ( 5, lineItem.getOperationsHold() );
                cstmt.setString ( 6, lineItem.getSalesHold() );
                cstmt.setString ( 7, userId );
                cstmt.addBatch();
            }

            cstmt.executeBatch();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not approve Credit Note Line Items", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
    }
}
